package db;


import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;

import core.Continent;
import core.Country;
import parsing.GeoParser;
public class CreateGeoTables {
	
	//### Constant Statements
	
	/* continents table- create, insert*/
	public static String dropContinentsTable="Drop table if exists continents;";
	public static String createContinentsTable=  "CREATE table continents( name varchar(100), id INT(11) NOT NULL ,PRIMARY KEY(id));";
	public static String updateContinentsTable= "INSERT into continents(name, id) Values(?,?);";
	
	/* countries table -create,insert*/
	public static String dropCountriesTable="Drop table if exists countries;";
	public static String createCountriesTable= "CREATE table countries( country_name varchar(100), country_id int(11) , continent_id int(11), PRIMARY KEY(country_id),constraint Foreign key (continent_id) references continents(id) on delete restrict on update restrict);";
	public static String updateCountriesTable="INSERT into countries(country_name, country_id, continent_id) Values(?,?,?);";
	
	/* cities table- create insert*/
	public static String dropCitiesTable="Drop table if exists cities;";
	public static String createCitiesTable= "CREATE table cities(countryID INT(11), city_name varchar(100),latitude float, longitude float ,city_id INT(11) NOT NULL auto_increment,PRIMARY KEY(city_id),"
			+ "UNIQUE(city_name,countryID));";
	//without foreign key added later";
	public static String updateCitiesTable="INSERT into cities(countryID ,city_name, latitude, longitude ) Values(?,?,?,?);";
	


    
	/**
	 * not the main of program!
	 * @param args file to parse from
	 * for use only when the tables are created in the first time
	 */
	public static void main(String[] args) {
		GeoParser.parserContinentCountryCity(args[0], 1);
	
		DB_SQL mySQL = new DB_SQL();
		mySQL.connect();
		try{
		mySQL.getConn().setAutoCommit(false);
		}catch (SQLException e){
			e.printStackTrace();
		}
		/* updating continents*/ 
		try {
		PreparedStatement pstm = mySQL.getConn().prepareStatement(updateContinentsTable);
			createContinentsTable(mySQL, pstm);
			mySQL.getConn().commit();
			pstm.close();
			// mySQL.getConn().commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		/* updating countries*/ 
		try {
			PreparedStatement pstm = mySQL.getConn().prepareStatement(updateCountriesTable);
			createCountriesTable(mySQL, pstm);
			mySQL.getConn().commit();
			pstm.close();
			// mySQL.getConn().commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		 

	}

	/* create continents table */
	public static void createContinentsTable(DB_SQL mySQL,
			PreparedStatement pstm) {
		Statement drop = DB_Tools.String2Statements(dropContinentsTable, mySQL.getStmt());

		if (mySQL.execStatment(drop) == 0) {
			System.out.println("Query Failed");
		} else {
			System.out.println("Query Succeeded");
		}
		Statement create = DB_Tools.String2Statements(createContinentsTable, mySQL.getStmt());

		if (mySQL.execStatment(create) == 0) {
			System.out.println("Query Failed");
		} else {
			System.out.println("Query Succeeded");
		}
		
		Object[] continents = GeoParser.continents.toArray();
		for (int i = 0; i < continents.length; i++) {
			if (addNewContinent((Continent) continents[i], pstm) == 0) {
				System.out.println("Query Failed");
			}
		}
		try {
			pstm.executeBatch();

		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Query Failed");

		}

	}

	// ###################Continent ########################
	public static int addNewContinent(Continent newContinent,
			PreparedStatement pstm) {
		try {
			pstm.setString(1, newContinent.getContinentName());
			pstm.setLong(2, newContinent.getContinentId());
			pstm.addBatch();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	// ###################Country ########################

	public static void createCountriesTable(DB_SQL mySQL, PreparedStatement pstm) {
		Statement drop = DB_Tools.String2Statements(dropCountriesTable, mySQL.getStmt());

		if (mySQL.execStatment(drop) == 0) {
			System.out.println("Query Failed");
		}
		
		Statement create = DB_Tools.String2Statements(createCountriesTable, mySQL.getStmt());

		if (mySQL.execStatment(create) == 0) {
			System.out.println("Query Failed");
		}
		for (Map.Entry<String, Country> entry : GeoParser.countries.entrySet()) {
			if (addNewCountry(entry.getValue(), pstm) == 0) {
				System.out.println("Query Failed");
			}
		}
		try {
			pstm.executeBatch();

		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Query Failed");

		}
	}

	public static int addNewCountry(Country newCountry, PreparedStatement pstm) {
		try {
			pstm.setString(1, newCountry.getCountryName());
			pstm.setLong(2, newCountry.getCountryId());
			pstm.setLong(3, newCountry.getContinentId());
			pstm.addBatch();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}

		return 1;
	}
	
}
